import sys
import os
import logging
import json
from typing import Dict, List, Any, Optional, Tuple
from decimal import Decimal

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历及总体_自主创业率的占比(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历及总体，自主创业率的占比 - 指标计算函数
    
    ## 指标说明
    计算各学历层次毕业生中自主创业人数占比及总体自主创业率。
    1. 查询项目配置获取客户编码和项目年份
    2. 根据客户编码计算shard_tb_key用于数据筛选
    3. 查询各学历层次毕业生中毕业去向为'自主创业'的人数
    4. 计算各学历层次自主创业率及总体自主创业率
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "group": "本科毕业生",
                "count": 120,
                "ratio": 0.05
            },
            {
                "group": "专科毕业生",
                "count": 80,
                "ratio": 0.03
            },
            {
                "group": "总体",
                "count": 200,
                "ratio": 0.04
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历及总体，自主创业率的占比, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = client_code.lstrip('ABCDEFGHIJKLMNOPQRSTUVWXYZ').lstrip('0')
        if not shard_tb_key:
            raise ValueError(f"无效的client_code: {client_code}")
            
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询各学历层次自主创业人数
        education_levels = ['本科毕业生', '专科毕业生', '硕士研究生', '博士研究生']
        results = []
        total_entrepreneur_count = 0
        total_student_count = 0
        
        # 3.1 查询总体数据
        total_sql = f"""
        SELECT 
            (SELECT COUNT(*) 
             FROM dim_client_target_baseinfo_student_calc_{item_year} 
             WHERE shard_tb_key = %s AND grad_dest = '自主创业') AS entrepreneur_count,
            (SELECT COUNT(*) 
             FROM dim_client_target_baseinfo_student_calc_{item_year} 
             WHERE shard_tb_key = %s) AS total_count
        """
        total_result = db.fetchone(total_sql, (shard_tb_key, shard_tb_key))
        if not total_result:
            raise ValueError("未查询到总体数据")
            
        total_entrepreneur_count = int(total_result['entrepreneur_count'] or 0)
        total_student_count = int(total_result['total_count'] or 0)
        
        # 3.2 查询各学历层次数据
        for edu in education_levels:
            edu_sql = f"""
            SELECT 
                (SELECT COUNT(*) 
                 FROM dim_client_target_baseinfo_student_calc_{item_year} 
                 WHERE shard_tb_key = %s AND education = %s AND grad_dest = '自主创业') AS entrepreneur_count,
                (SELECT COUNT(*) 
                 FROM dim_client_target_baseinfo_student_calc_{item_year} 
                 WHERE shard_tb_key = %s AND education = %s) AS total_count
            """
            edu_result = db.fetchone(edu_sql, (shard_tb_key, edu, shard_tb_key, edu))
            if not edu_result:
                logger.warning(f"未查询到学历层次 {edu} 的数据")
                continue
                
            entrepreneur_count = int(edu_result['entrepreneur_count'] or 0)
            student_count = int(edu_result['total_count'] or 0)
            
            if student_count > 0:
                ratio = entrepreneur_count / student_count
            else:
                ratio = 0.0
                
            results.append({
                "group": edu,
                "count": entrepreneur_count,
                "ratio": float(round(ratio, 4))
            })
        
        # 计算总体比例
        if total_student_count > 0:
            total_ratio = total_entrepreneur_count / total_student_count
        else:
            total_ratio = 0.0
            
        results.append({
            "group": "总体",
            "count": total_entrepreneur_count,
            "ratio": float(round(total_ratio, 4))
        })

        logger.info(f"指标 '各学历及总体，自主创业率的占比' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": results
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历及总体，自主创业率的占比' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历及总体，自主创业率的占比",
            "code": 500,
            "error": str(e)
        }